package cn.hpclub.server.model;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

/**
 * 实体类 - 订单（大掌柜app主题广场中除宽带预约以外的其他6种订单）
 * 
 */
@TableBind(tableName = "sales_promotion")
public class SalesPromotion extends Model<SalesPromotion> implements Serializable{
    private static Logger              logger           = LoggerFactory.getLogger(SalesPromotion.class);
    private static final long          serialVersionUID = 5108597504836776211L;
    public static final SalesPromotion dao              = new SalesPromotion();

    public List<Record> queryAllOrderData(int dyid, int type, String starttime, String endtime){
        String sql = "select date_format(time, '%Y-%m-%d') as SaleDate,count(time) as SaleAmount from sales_promotion where"
                + " business_code !='1895' and appid='" + Constant.APPID + "' and userid=" + "?"
                + " and date_format(time, '%Y-%m-%d') >= date_format('" + starttime
                + "', '%Y-%m-%d') and date_format(time, '%Y-%m-%d') <= date_format('" + endtime
                + "', '%Y-%m-%d') and moduleId=" + "?" + " and return_code='0' group by SaleDate desc;";
        Object[] params = new Object[] { dyid, type };

        return Db.find(sql, params);
    }

    public String getSelectStatementForCMCCBussiness(){
        String sqlExceptSelect = "select m.emp_code,(" +
                "CASE m.member_type  WHEN 1 THEN  '店长' WHEN 2 THEN '店员' WHEN 3 THEN  '泛店员'" +
                " ELSE  '未知'  END )AS member_type," + InterfaceUtil.getCitySql("sp.org_id") + " as city, "
                + InterfaceUtil.getAreaSql("sp.org_id") + " as area, "
                + " NumCode, DisplayName,  (select class_name from organization_class where enabled = '1' and id = org.webSiteId) as sitetype,  "
                + " order_id, sp.moduleCode as moudlecode,sp.time as time,thirdpart_id, "
                + " (case sp.moduleId when 1 then '充值缴费' when 2 then '实名开卡' when 3 then '选号入网' when 4 then '资费办理' when 5 then '实名补登记' when 6 then '营销活动' end ) as moudlename, "
                // 添加saletype字段
                + " (case sp.saletype when 0 then '未知' when 1 then 'APP' when 2 then '分享页面' when 3 then '好友圈' else '未知' end) as saletype,"
                + " business_code, business_name as tariff, "
                + " (sp.amount/100) as total_amount, m.mobile as waitermobile, m.name as waitername, phone_no, "
                + " date_format(time, '%Y-%m-%d') as cmcc_date, date_format(time, '%H:%i:%s') as cmcc_time, reward, "
                + " IF(orgType.orgIDTmp || orgType2.orgIDTmp2 , '是', '否') AS is_accounts_manager ";
        return sqlExceptSelect;
    }

    public String getSelectStatementForCMCCReward(){
        String select = "select 'B3' as businessType, (case business_code when 'J616' then '' when'1895' then '' else sp.business_code end) as business_code, business_name as tariff, phone_no, '' as amount,"
                + " date_format(time, '%Y%m%d%H%i%s') as cmcc_time, 1 as resultCode, order_id, NumCode, (case when sp.moduleCode is null then sp.business_code else sp.moduleCode end) as moudlecode,"
                + " '' as paramter, 'ZF002' as rewardTypeCode, 2 as qudaoFlag, '大掌柜' as qudaoName";
        return select;
    }

    public String getExceptSelectStatementForCMCCBussiness(ConcurrentHashMap<String, String> searchInfo){
        // isDelete 是否删除(0:正常数据 1:已删除 2:新东网 3:预演测试数据 4测试数据 5退订数据)
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = " FROM sales_promotion sp left join member_org m on sp.userid = m.id "
                + " LEFT JOIN organization org on sp.org_id = org.id and org.is_deleted = 0 "
                // + " LEFT JOIN business bus on bus.businessCode =
                // sp.business_code "
                + " LEFT JOIN (SELECT id AS orgIDTmp FROM `organization` where (parentid=" + topId
                + " OR parentid IN (select id FROM organization WHERE  parentid=" + topId
                + "))) AS orgType ON sp.org_id = orgType.orgIDTmp"
                + " LEFT JOIN (SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org, organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled=1 AND orgcls.enabled=1 AND ( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800')) AS orgType2 ON sp.org_id = orgType2.orgIDTmp2"
                + " WHERE sp.isDelete = 0 and sp.return_code = '0'  and sp.business_code !='1895' ";
        String moduleName = "";
        for(Entry<String, String> item : searchInfo.entrySet()){
            if(item.getKey().equals("moduleName")){
                moduleName = item.getValue();
                continue;
            }
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += "  ";
        if(StrKit.notBlank(moduleName)){
            //sqlExceptSelect += " AND moudlename like '%" + moduleName + "%'";
            sqlExceptSelect += " AND (CASE sp.moduleId WHEN 1 THEN '充值缴费' WHEN 2 THEN '实名开卡' WHEN 3 THEN '选号入网' WHEN 4 THEN '资费办理' WHEN 5 THEN '实名补登记' WHEN 6 THEN '营销活动' END ) like '%" + moduleName + "%'";
        }
        sqlExceptSelect += " order by sp.time desc ";
        return sqlExceptSelect;
    }

    /**
     * @Title: getExceptSelectStatementForCmccReward
     * @Description:Cmcc酬金数据导出（排除充值缴费、预登记激活）
     * @param searchInfo
     * @return String
     */
    public String getExceptSelectStatementForCmccReward(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from sales_promotion sp left join member_org m on sp.userid = m.id and m.enabled = 1 "
                + " left join organization org on sp.org_id = org.id and org.is_deleted = 0 "
                // + " left join business bus on bus.businessCode =
                // sp.business_code "
                + " where sp.isDelete = 0 and sp.return_code = '0' and business_code != 'J616' and business_code != '4001' ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " order by sp.time desc";

        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("org_id")){
            sql += " and sp.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        } else if(item.getKey().equals("member_type") && Integer.parseInt(item.getValue())>0){
            sql +=" AND m.member_type= "+item.getValue();
        }else if(item.getKey().equals("phonenumber") && StrKit.notBlank(item.getValue())){
            sql += " and (sp.phone_no like '%" + item.getValue() + "%' or m.mobile like '%" + item.getValue() + "%') ";
        } else if(item.getKey().equals("startTime")){
            sql += " and sp.time >= '" + item.getValue() + " 00:00:00' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and sp.time <= '" + item.getValue() + " 23:59:59' ";
        } else if(item.getKey().equals("tariffName") && StrKit.notBlank(item.getValue())){
            sql += " and business_name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("waiterName") && StrKit.notBlank(item.getValue())){
            sql += " and m.name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("siteCode")){
            sql += " and NumCode = '" + item.getValue() + "' ";
        } else if(item.getKey().equals("siteName") && StrKit.notBlank(item.getValue())){
            sql += " and DisplayName like '%" + item.getValue() + "%' ";
        } else if(item.getKey().startsWith("busin" +
                "essCodeNotEqual")){
            sql += " and business_code != '" + item.getValue() + "' ";
        } else if(item.getKey().startsWith("isMgr")){
            if("1".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is not null) or (orgType2.orgIDTmp2 is not null)) ";
            } else if("0".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is  null) and (orgType2.orgIDTmp2 is null)) ";
            }

        } else if(item.getKey().equals("moduleCode") && StrKit.notBlank(item.getValue())){
            sql += " and sp.moduleCode like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("orderNumber") && StrKit.notBlank(item.getValue())){
            sql += " and (order_id like '%" + item.getValue() + "%' or thirdpart_id like'%" + item.getValue() + "%')";
        }

        return sql;
    }

    public String getSelectStatementForCMCCBussinessDataSynchronization(){
        String select = " select " + InterfaceUtil.getCitySql("sp.org_id") + " as city, "
                + InterfaceUtil.getAreaSql("sp.org_id") + " as area, "
                + " NumCode, DisplayName,  (select class_name from organization_class where enabled = '1' and id = org.webSiteId) as sitetype,  "
                + " order_id, sp.moduleCode as moudlecode, "
                + " (case sp.moduleId when 1 then '充值缴费' when 2 then '实名开卡' when 3 then '选号入网' when 4 then '资费办理' when 5 then '实名补登记' when 6 then '营销活动' end ) as moudlename, "
                + " business_code, business_name as tariff, "
                + " (sp.amount/100) as total_amount, m.mobile as waitermobile, m.name as waitername, phone_no, "
                + " date_format(time, '%Y-%m-%d') as cmcc_date, date_format(time, '%H:%i:%s') as cmcc_time, reward ";

        return select;
    }

    public String getExceptSelectStatementForCMCCBussinessDataSynchronization(String startTime, String endTime){
        String sqlExceptSelect = " from sales_promotion sp left join member_org m on sp.userid = m.id "
                + " left join organization org on sp.org_id = org.id and org.is_deleted = 0 "
                // + " left join business bus on bus.businessCode =
                // sp.business_code "
                + " where sp.isDelete = 0 and sp.return_code = '0' ";

        int chengduId = InterfaceUtil.getCityOrgId("成都");
        logger.debug("chengduId=" + chengduId);
        if(0 != chengduId){
            sqlExceptSelect += " and sp.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(chengduId, Constant.MAX_ORG_LEVEL, true) + ") ";
        }
        sqlExceptSelect += " and sp.time >= '" + startTime + " 00:00:00' ";
        sqlExceptSelect += " and sp.time <= '" + endTime + " 23:59:59' ";
        sqlExceptSelect += " order by sp.time desc";
        return sqlExceptSelect;
    }

    public Page<SalesPromotion> findReportByPage(int num, int size, String select, String sqlExceptSelect){

        Page<SalesPromotion> page = paginate(num, size, select, sqlExceptSelect);

        return page;
    }

    public Long getCmccOrderCount(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String today = sdf.format(new Date());
        // int org_id = InterfaceUtil.getOrgId();

        String sql = " select count(1) as orderCnt from sales_promotion as sp where sp.isDelete = 0 and sp.return_code = '0' "
                // + " and sp.org_id in(" +
                // InterfaceUtil.getChlidOrgIdSql(org_id,
                // Constant.MAX_ORG_LEVEL, true) + ") "
                + " and date_format(sp.time,'%Y-%m-%d') = ? ";
        Record orderCnt = Db.findFirst(sql, today);
        Long count = orderCnt.get("orderCnt");

        return count;
    }

}
